IF OBJECT_ID('dbo.fnGetStartEndofWeek') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetStartEndofWeek
    IF OBJECT_ID('dbo.fnGetStartEndofWeek') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetStartEndofWeek >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetStartEndofWeek >>>'
END
go
-- =======================================================================
-- Function Name: dbo.fnGetStartEndofWeek()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/14/2008
--
-- Description: Returns StartDate and EndDate for the week containing
--		the given date. Extra param gives first day of the week.
--
-- Parameters:  @Date, @StartOfWeek 
--              
-- Returns:     TABLE (see defn below) 
--
-- SampleCall:
-- SELECT * FROM dbo.fnGetStartEndofWeek ( GETDATE(), 'MON' )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/14/08     KC        Created
--
-- =======================================================================
CREATE FUNCTION dbo.fnGetStartEndofWeek
(
     @AsOfDate	     	DATETIME, 
     @FirstDayOfWeek	CHAR(3)
)
RETURNS @Week TABLE
(
     StartDate			DATETIME,
     EndDate			DATETIME
)
AS
BEGIN
	-- Remove Time component
	SET	@AsOfDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),@AsOfDate))
	SET	@FirstDayOfWeek = UPPER(@FirstDayOfWeek)
	
	-- Validate Parms	
	IF @FirstDayOfWeek NOT IN ('MON','TUE','WED','THU','FRI','SAT','SUN')
		RETURN

	-- Step back to beginning of Week.
	WHILE @FirstDayOfWeek != UPPER(LEFT(DATENAME(dw,@AsOfDate),3))
	BEGIN
		SET	@AsOfDate = DATEADD(DD,-1,@AsOfDate)
	END
	
	INSERT    @Week
		(StartDate,
		EndDate)
	VALUES
		(@AsOfDate,
		DATEADD(DD,6,@AsOfDate))
	
	RETURN

END
go
IF OBJECT_ID('dbo.fnGetStartEndofWeek') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetStartEndofWeek >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetStartEndofWeek >>>'
go